package com.xiaoma.tpo.dao;

import java.util.List;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Repository;

import com.xiaoma.tpo.baseDao.BaseDaoImpl;
import com.xiaoma.tpo.baseDao.DefaultRowMapper;
import com.xiaoma.tpo.domain.ForumTopic;
import com.xiaoma.tpo.dto.ForumTeacherTopicVo;
import com.xiaoma.tpo.dto.ForumTopicVo;
import com.xiaoma.tpo.dto.TopicSum;
import com.xiaoma.tpo.utils.Pager;

@Repository
public class ForumTopicDao extends BaseDaoImpl<ForumTopic>{

    private static final Logger LOG = LoggerFactory.getLogger(ForumTopicDao.class);
    
    public Pager findTopics(int startIndex, int itemsPerPage) {
        Pager pager = new Pager();
        Object[] params = new Object[]{startIndex, itemsPerPage};
        String sqlQuery = "SELECT a.id, a.user_id, IFNULL(b.nickname,b.name) user_name, b.avatar, a.title, a.content, a.created_at, a.top, a.post_count, a.imgs, a.type, a.read_count, a.share_count, a.store_count FROM forum_topic a left join user b on a.user_id = b.id order by a.top desc, a.updated_at desc limit ?,?";
        String sqlCount = "SELECT count(1) from forum_topic";
        
        LOG.info(sqlQuery + " PARAMS: "  + startIndex + ", " + itemsPerPage);
        LOG.info(sqlCount);
        
        List<?> list = jdbcTemplate.query(sqlQuery,params, new DefaultRowMapper(ForumTopicVo.class, getNameHandler()));
        int totalCount = jdbcTemplate.queryForInt(sqlCount);
        pager.setList(list);
        pager.setTotalCount(totalCount);

        return pager;
    }
    
    public Pager findTopics(int startIndex, int itemsPerPage, int type) {
        Pager pager = new Pager();
        Object[] params = new Object[]{type, startIndex, itemsPerPage};
        String sqlQuery = "SELECT a.id, a.user_id, IFNULL(b.nickname,b.name) user_name, b.avatar, a.title, a.content, a.created_at, a.top, a.post_count, a.imgs, a.type, a.read_count, a.share_count, a.store_count FROM forum_topic a left join user b on a.user_id = b.id where a.type = ? order by a.top desc, a.updated_at desc limit ?,?";
        String sqlCount = "SELECT count(1) from forum_topic where type = ?";
        
        LOG.info(sqlQuery + " PARAMS: "  + startIndex + ", " + itemsPerPage + ", " + type);
        LOG.info(sqlCount + " PARAMS:" + type);
        
        List<?> list = jdbcTemplate.query(sqlQuery,params, new DefaultRowMapper(ForumTopicVo.class, getNameHandler()));
        int totalCount = jdbcTemplate.queryForInt(sqlCount, new Object[]{type});
        pager.setList(list);
        pager.setTotalCount(totalCount);
        
        return pager;
    }
    
    public Pager findTeacherTopics(int startIndex, int itemsPerPage) {
        Pager pager = new Pager();
        Object[] params = new Object[]{startIndex, itemsPerPage};
        String sqlQuery = "SELECT a.id, b.name as user_name, a.title, a.content, a.created_at, a.top, a.post_count, a.imgs, a.type FROM forum_topic a left join user b on a.user_id = b.id order by a.top desc, a.updated_at desc limit ?,?";
        String sqlCount = "SELECT count(1) from forum_topic";
        
        LOG.info(sqlQuery + " PARAMS: "  + startIndex + ", " + itemsPerPage);
        LOG.info(sqlCount);
        
        List<?> list = jdbcTemplate.query(sqlQuery,params, new DefaultRowMapper(ForumTeacherTopicVo.class, getNameHandler()));
        int totalCount = jdbcTemplate.queryForInt(sqlCount);
        pager.setList(list);
        pager.setTotalCount(totalCount);
        
        return pager;
    }
    
    @SuppressWarnings("unchecked")
    public List<ForumTopicVo> findOldTopics(int topicId, int itemsPerPage) {
        Object[] params = new Object[]{topicId, itemsPerPage};
        String sqlQuery = "SELECT a.id, a.user_id, IFNULL(b.nickname,b.name) user_name, b.avatar, a.title, a.content, a.created_at, a.top, a.post_count, a.imgs, a.type, a.read_count, a.share_count, a.store_count FROM forum_topic a left join user b on a.user_id = b.id where a.id < ? and a.top = false order by id desc limit ?";
        List<?> list = jdbcTemplate.query(sqlQuery,params, new DefaultRowMapper(ForumTopicVo.class, getNameHandler()));
        return (List<ForumTopicVo>)list;
    }
    
    @SuppressWarnings("unchecked")
    public List<ForumTopicVo> findOldTopics(int topicId, int itemsPerPage, int type) {
        Object[] params = new Object[]{topicId, type, itemsPerPage};
        String sqlQuery = "SELECT a.id, a.user_id, IFNULL(b.nickname,b.name) user_name, b.avatar, a.title, a.content, a.created_at, a.top, a.post_count, a.imgs, a.type, a.read_count, a.share_count, a.store_count FROM forum_topic a left join user b on a.user_id = b.id where a.id < ? and type = ? and a.top = false order by id desc limit ?";
        List<?> list = jdbcTemplate.query(sqlQuery,params, new DefaultRowMapper(ForumTopicVo.class, getNameHandler()));
        return (List<ForumTopicVo>)list;
    }
    
    public int limitTopNum(Integer num) {
        String sql = "update forum_topic set top = false where id not in(select id from ((select id from forum_topic where top = true order by updated_at desc limit ?) as foo))";
        LOG.info("SQL:{}; topicId:{}",sql, num);
        return jdbcTemplate.update(sql,num);
    }
    
    public void deleteTopic(int topicId) {
        String sql = "delete from forum_topic where id = " + topicId;
        String sql1 = "delete from forum_post where topic_id = " + topicId;
        jdbcTemplate.execute(sql);
        jdbcTemplate.execute(sql1);
    }
    
    public TopicSum topicSum(int userId) {
        String sql = "select sum(read_count) read_total, sum(share_count) share_total, sum(store_count) store_total from forum_topic where user_id = ? group by null";
        List<?> list = jdbcTemplate.query(sql,new DefaultRowMapper(TopicSum.class, getNameHandler()), userId);
        return list.size() > 0 ? (TopicSum)list.get(0) : null;
    }
    
    @SuppressWarnings("unchecked")
    public List<ForumTopic> findByUserId(int userId) {
        String sql = "select * from forum_topic where user_id = ? order by id desc";
        List<?> items = jdbcTemplate.query(sql, new DefaultRowMapper(ForumTopic.class, getNameHandler()), userId);
        return (List<ForumTopic>)items;
    }
    
//    @SuppressWarnings("unchecked")
//    public List<ForumTopic> findResponsedTopics(int userId) {
//        String sql = "select * from forum_topic where user_id = ? and (post_count > 0 or store_count > 0) order by id";
//        List<?> items = jdbcTemplate.query(sql, new DefaultRowMapper(ForumTopic.class, getNameHandler()), userId);
//        return (List<ForumTopic>)items;
//    }
//    
//    @SuppressWarnings("unchecked")
//    public List<ForumTopic> findJoinedTopics(int userId) {
//        String sql = "select * from forum_topic where user_id != ? and id in (select DISTINCT a.topic_id from forum_post a inner join forum_post b on a.id = b.parent_id where a.parent_id = 0 and b.parent_id > 0 and a.user_id = ? and b.user_id != ?) order by id";
//        List<?> items = jdbcTemplate.query(sql, new DefaultRowMapper(ForumTopic.class, getNameHandler()), userId, userId, userId);
//        return (List<ForumTopic>)items;
//    }
    
    @SuppressWarnings("unchecked")
    public List<ForumTopic> findJoinedTopics(int userId) {
        String sql = "select * from forum_topic where user_id = ? and (post_count > 0 or store_count > 0)" + 
        " union (select * from forum_topic where user_id != ? and id in (select DISTINCT a.topic_id from forum_post a inner join forum_post b on a.id = b.parent_id where a.parent_id = 0 and b.parent_id > 0 and a.user_id = ? and b.user_id != ?)) order by id";
        List<?> items = jdbcTemplate.query(sql, new DefaultRowMapper(ForumTopic.class, getNameHandler()), userId, userId,userId,userId);
        return (List<ForumTopic>)items;
    }
    
    public ForumTopicVo findTopic(int topicId) {
        String sql = "SELECT a.id, a.user_id, IFNULL(b.nickname,b.name) user_name, b.avatar, a.title, a.content, a.created_at, a.top, a.post_count, a.imgs, a.type, a.read_count, a.share_count, a.store_count FROM forum_topic a left join user b on a.user_id = b.id where a.id = ?";
        List<?> items = jdbcTemplate.query(sql, new DefaultRowMapper(ForumTopicVo.class, getNameHandler()), topicId);
        return items.size() > 0 ? (ForumTopicVo)items.get(0) : null;
    }
    
    @SuppressWarnings("unchecked")
    public List<ForumTopic> findTopics(int number) {
        String sql = "select * from forum_topic order by id desc limit ?";
        List<?> items = jdbcTemplate.query(sql, new DefaultRowMapper(ForumTopic.class, getNameHandler()), number);
        return (List<ForumTopic>)items;
    }
    
    @SuppressWarnings("unchecked")
    public List<ForumTopic> findTopicsByType(int type) {
        String sql = "select * from forum_topic where type = ? order by id desc";
        List<?> items = jdbcTemplate.query(sql, new DefaultRowMapper(ForumTopic.class, getNameHandler()), type);
        return (List<ForumTopic>)items;
    }
    
}
